package com.rockcent.service.impl;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.rockcent.common.common.PageResult;
import com.rockcent.common.common.SQLListDoing;
import com.rockcent.common.common.SQLObjectDoing;
import com.rockcent.common.common.SQLPageDoing;
import com.rockcent.mall.lib.domain.enumclass.MerchantOrderSourceType;
import com.rockcent.mall.lib.domain.enumclass.ProductSourceType;
import com.rockcent.mall.lib.domain.enumclass.ProductType;
import com.rockcent.mall.lib.dto.MerchantProductStatisticsDto;
import com.rockcent.service.MerchantProductStatisticsService;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * Created by Marco on 16-12-15.
 * 产品统计
 */
@Service
public class MerchantProductStatisticsServiceImpl implements MerchantProductStatisticsService {

    private Logger log = LoggerFactory.getLogger(MerchantProductStatisticsServiceImpl.class);

    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;
    @Autowired
    private ObjectMapper objectMapper;

    /**
     * 展示次数
     * @param mallId
     * @param merchantId
     * @param productId
     * @param sourceType
     * @param type
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public Long getProductShowCount(Long mallId, Long merchantId, Long productId, String sourceType, String type, String startDate, String endDate) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM product_event pe");
            sql.append(" LEFT JOIN product p ON p.id=pe.`product_id`");
            sql.append(" WHERE pe.is_delete='NO'");
            sql.append(" AND pe.buyer_id is not null");
            sql.append(" AND pe.`mall_id`=:mallId");
            sql.append(" AND pe.`merchant_id`=:merchantId");
            sql.append(" AND pe.`event_type` = 'VIEWED'");

            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            if(productId!=null && productId.longValue()>0) {
                sql.append(" AND pe.`product_id`=:productId");
                params.put("productId", productId);
            }

            if (sourceType != null && MerchantOrderSourceType.ALL != MerchantOrderSourceType.valueOf(sourceType)) {
                if (MerchantOrderSourceType.SELF == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append(" AND p.market_id is null");
                }
                if (MerchantOrderSourceType.MARKET == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append(" AND p.market_id is not null");
                }
            }

            if (type != null && ProductType.valueOf(type) != ProductType.ALL) {
                sql.append(" AND p.type =:type");
                params.put("type", type);
            }
            if (startDate != null) {
                sql.append(" AND pe.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND pe.date_created<=:endDate");
                params.put("endDate", endDate);

            }
            fields.append("count(1)");
        };
        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 访问人数
     * @param mallId
     * @param merchantId
     * @param productId
     * @param sourceType
     * @param type
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public Long getProductViewCustomerCount(Long mallId, Long merchantId, Long productId, String sourceType, String type, String startDate, String endDate, Long channelId) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM product_event pe");
            sql.append(" LEFT JOIN product p ON p.id=pe.`product_id`");
            sql.append(" WHERE pe.is_delete='NO'");
            sql.append(" AND pe.buyer_id is not null");
            sql.append(" AND pe.`mall_id`=:mallId");
            sql.append(" AND pe.`merchant_id`=:merchantId");
            sql.append("  AND pe.`event_type` IN('CLICKED')");

            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            if(channelId!=null && channelId.longValue()>0) {
                sql.append(" AND pe.channel=:channelId");
                params.put("channelId", channelId);
            }

            if(productId!=null && productId.longValue()>0) {
                sql.append(" AND pe.`product_id`=:productId");
                params.put("productId", productId);
            }

            if (sourceType != null && MerchantOrderSourceType.ALL != MerchantOrderSourceType.valueOf(sourceType)) {
                if (MerchantOrderSourceType.SELF == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append(" AND p.market_id is null");
                }
                if (MerchantOrderSourceType.MARKET == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append(" AND p.market_id is not null");
                }
            }

            if (type != null && ProductType.valueOf(type) != ProductType.ALL) {
                sql.append(" AND p.type =:type");
                params.put("type", type);
            }
            if (startDate != null) {
                sql.append(" AND pe.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND pe.date_created<=:endDate");
                params.put("endDate", endDate);

            }
            fields.append("count(distinct pe.buyer_id)");
        };
        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 访问次数
     * @param mallId
     * @param merchantId
     * @param productId
     * @param sourceType
     * @param type
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public Long getProductViewCount(Long mallId, Long merchantId, Long productId, String sourceType, String type, String startDate, String endDate, Long channelId) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM product_event pe");
            sql.append(" LEFT JOIN product p ON p.id=pe.`product_id`");
            sql.append(" WHERE pe.is_delete='NO'");
            sql.append(" AND pe.buyer_id is not null");
            sql.append(" AND pe.`mall_id`=:mallId");
            sql.append(" AND pe.`merchant_id`=:merchantId");
            sql.append(" AND pe.`event_type` = 'CLICKED'");

            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            if(channelId!=null && channelId.longValue()>0) {
                sql.append(" AND pe.channel=:channelId");
                params.put("channelId", channelId);
            }

            if(productId!=null && productId.longValue()>0) {
                sql.append(" AND pe.`product_id`=:productId");
                params.put("productId", productId);
            }

            if (sourceType != null && MerchantOrderSourceType.ALL != MerchantOrderSourceType.valueOf(sourceType)) {
                if (MerchantOrderSourceType.SELF == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append(" AND p.market_id is null");
                }
                if (MerchantOrderSourceType.MARKET == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append(" AND p.market_id is not null");
                }
            }

            if (type != null && ProductType.valueOf(type) != ProductType.ALL) {
                sql.append(" AND p.type =:type");
                params.put("type", type);
            }
            if (startDate != null) {
                sql.append(" AND pe.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND pe.date_created<=:endDate");
                params.put("endDate", endDate);

            }
            fields.append("count(1)");
        };
        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 下单数量
     * @param mallId
     * @param merchantId
     * @param productId
     * @param sourceType
     * @param type
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public Long getProductSaleCount(Long mallId, Long merchantId, Long productId, String sourceType, String type, String startDate, String endDate, Long channelId) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM merchant_order_item moi");
            sql.append(" LEFT JOIN product p ON p.id=moi.`product_id`");
            if(channelId!=null && channelId.longValue()>0) {
                sql.append(" LEFT JOIN channel_product cp ON p.id=cp.`product_id`");
            }
            sql.append(" WHERE moi.is_delete='NO'");
            sql.append(" AND moi.`mall_id`=:mallId");
            sql.append(" AND moi.`merchant_id`=:merchantId");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            if(channelId!=null && channelId.longValue()>0) {
                sql.append(" AND cp.channel_id=:channelId");
                params.put("channelId", channelId);
            }
            if (sourceType != null && MerchantOrderSourceType.ALL != MerchantOrderSourceType.valueOf(sourceType)) {
                if (MerchantOrderSourceType.SELF == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is null");
                }
                if (MerchantOrderSourceType.MARKET == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is not  null");
                }
            }

            if(productId!=null && productId.longValue()>0) {
                sql.append(" AND moi.`product_id`=:productId");
                params.put("productId", productId);
            }

            if (type != null && ProductType.valueOf(type) != ProductType.ALL) {
                sql.append(" AND p.`type` =:type");
                params.put("type", type);
            }
            if (startDate != null) {
                sql.append(" AND moi.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND moi.date_created<=:endDate");
                params.put("endDate", endDate);

            }
            fields.append("sum(moi.buy_count)");
        };
        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 支付数量
     * @param mallId
     * @param merchantId
     * @param productId
     * @param sourceType
     * @param type
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public Long getProductSalePayCount(Long mallId, Long merchantId, Long productId, String sourceType, String type, String startDate, String endDate, Long channelId) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM merchant_order_item moi");
            sql.append(" LEFT JOIN product p ON p.id=moi.`product_id`");

            sql.append(" left join order_event oe on moi.merchant_order_num=oe.merchant_order_num");
            sql.append(" WHERE moi.is_delete='NO'");
            sql.append(" AND oe.event_type='PAYED'");
            sql.append(" AND moi.`mall_id`=:mallId");
            sql.append(" AND moi.`merchant_id`=:merchantId");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            if(channelId!=null && channelId.longValue()>0) {
                sql.append(" AND moi.channel_id=:channelId");
                params.put("channelId", channelId);
            }
            if (sourceType != null && MerchantOrderSourceType.ALL != MerchantOrderSourceType.valueOf(sourceType)) {
                if (MerchantOrderSourceType.SELF == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is null");
                }
                if (MerchantOrderSourceType.MARKET == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is not  null");
                }
            }

            if(productId!=null && productId.longValue()>0) {
                sql.append(" AND moi.`product_id`=:productId");
                params.put("productId", productId);
            }

            if (type != null && ProductType.valueOf(type) != ProductType.ALL) {
                sql.append(" AND p.`type` =:type");
                params.put("type", type);
            }
            if (startDate != null) {
                sql.append(" AND moi.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND moi.date_created<=:endDate");
                params.put("endDate", endDate);

            }
            fields.append("sum(moi.buy_count)");
        };
        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 核销人数
     * @param mallId
     * @param merchantId
     * @param productId
     * @param sourceType
     * @param type
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public Long getProductVerifyCustomerCount(Long mallId, Long merchantId, Long productId, String sourceType, String type, String startDate, String endDate, Long channelId) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM merchant_order_item moi");
            sql.append(" LEFT JOIN product p ON p.id=moi.`product_id`");

            sql.append(" left join merchant_order mo on mo.id=moi.merchant_order_id");
            sql.append(" WHERE moi.is_delete='NO'");
            sql.append(" and moi.verify_count > 0");
            sql.append(" AND moi.`mall_id`=:mallId");
            sql.append(" AND moi.`merchant_id`=:merchantId");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            if(channelId!=null && channelId.longValue()>0) {
                sql.append(" AND moi.channel_id=:channelId");
                params.put("channelId", channelId);
            }

            if (sourceType != null && MerchantOrderSourceType.ALL != MerchantOrderSourceType.valueOf(sourceType)) {
                if (MerchantOrderSourceType.SELF == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is null");
                }
                if (MerchantOrderSourceType.MARKET == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is not  null");
                }
            }

            if(productId!=null && productId.longValue()>0) {
                sql.append(" AND moi.`product_id`=:productId");
                params.put("productId", productId);
            }

            if (type != null && ProductType.valueOf(type) != ProductType.ALL) {
                sql.append(" AND p.`type` =:type");
                params.put("type", type);
            }
            if (startDate != null) {
                sql.append(" AND moi.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND moi.date_created<=:endDate");
                params.put("endDate", endDate);

            }
            fields.append("count(distinct mo.`customer_id`)");
        };

        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 核销数量
     * @param mallId
     * @param merchantId
     * @param productId
     * @param sourceType
     * @param type
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public Long getProductVerifyCount(Long mallId, Long merchantId, Long productId, String sourceType, String type, String startDate, String endDate, Long channelId) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM merchant_order_item moi");
            sql.append(" LEFT JOIN product p ON p.id=moi.`product_id`");
            sql.append(" left join merchant_order mo on mo.id=moi.merchant_order_id");
            sql.append(" WHERE moi.is_delete='NO'");
            sql.append(" and moi.verify_count > 0");
            sql.append(" AND moi.`mall_id`=:mallId");
            sql.append(" AND moi.`merchant_id`=:merchantId");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            if(channelId!=null && channelId.longValue()>0) {
                sql.append(" AND moi.channel_id=:channelId");
                params.put("channelId", channelId);
            }
            if (sourceType != null && MerchantOrderSourceType.ALL != MerchantOrderSourceType.valueOf(sourceType)) {
                if (MerchantOrderSourceType.SELF == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is null");
                }
                if (MerchantOrderSourceType.MARKET == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is not  null");
                }
            }

            if(productId!=null && productId.longValue()>0) {
                sql.append(" AND moi.`product_id`=:productId");
                params.put("productId", productId);
            }

            if (type != null && ProductType.valueOf(type) != ProductType.ALL) {
                sql.append(" AND p.`type` =:type");
                params.put("type", type);
            }
            if (startDate != null) {
                sql.append(" AND moi.date_created>=:startDate");
                params.put("startDate", startDate);
            }
            if (endDate != null) {
                sql.append(" AND moi.date_created<=:endDate");
                params.put("endDate", endDate);
            }
            fields.append("sum(moi.verify_count)");
        };
        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 按月访问次数
     * @param mallId
     * @param merchantId
     * @param productId
     * @param sourceType
     * @param type
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public List<MerchantProductStatisticsDto> getProductViewCountByMonth(Long mallId, Long merchantId, Long productId, String sourceType, String type, String startDate, String endDate, String groupBy) {
        SQLListDoing<MerchantProductStatisticsDto> doing = (sql, fields, params, typeReferenceMap) -> {
            String dateFormat = "date_format(pe.date_created,'%Y/%m/%d')";
            String groupName = "date(pe.date_created)";
            if("hour".equals(groupBy)){
                dateFormat = "date_format(pe.date_created,'%H')";
                groupName="date_format(pe.date_created,'%H')";
            } else if("day".equals(groupBy)) {
                dateFormat = "date_format(pe.date_created,'%Y/%m/%d')";
                groupName="date(pe.date_created)";
            } else if("month".equals(groupBy)) {
                dateFormat = "date_format(pe.date_created,'%Y/%m')";
                groupName="date_format(pe.date_created,'%Y/%m')";
            }
            sql.append("SELECT "+dateFormat+" as dateStr,'visit' as chartType,count(1) as quantity FROM product_event pe");
            sql.append(" LEFT JOIN product p ON p.id=pe.`product_id`");
            sql.append(" WHERE pe.is_delete='NO'");
            sql.append(" AND pe.buyer_id is not null");
            sql.append(" AND pe.`event_type` = 'CLICKED'");
            sql.append(" AND pe.`mall_id`=:mallId");
            sql.append(" AND pe.`merchant_id`=:merchantId");

            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            if(productId!=null && productId.longValue()>0) {
                sql.append(" AND pe.`product_id`=:productId");
                params.put("productId", productId);
            }

            if (sourceType != null && MerchantOrderSourceType.ALL != MerchantOrderSourceType.valueOf(sourceType)) {
                if (MerchantOrderSourceType.SELF == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append(" AND p.market_id is null");
                }
                if (MerchantOrderSourceType.MARKET == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append(" AND p.market_id is not null");
                }
            }

            if (type != null && ProductType.valueOf(type) != ProductType.ALL) {
                sql.append(" AND p.type =:type");
                params.put("type", type);
            }
            if (startDate != null) {
                sql.append(" AND pe.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND pe.date_created<=:endDate");
                params.put("endDate", endDate);

            }
            sql.append(" group by "+groupName);
        };
        return doing.go(MerchantProductStatisticsDto.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 按月下单数量
     * @param mallId
     * @param merchantId
     * @param productId
     * @param sourceType
     * @param type
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public List<MerchantProductStatisticsDto> getProductSaleCountByMonth(Long mallId, Long merchantId, Long productId, String sourceType, String type, String startDate, String endDate, String groupBy) {
        SQLListDoing<MerchantProductStatisticsDto> doing = (sql, fields, params, typeReferenceMap) -> {
            String dateFormat = "date_format(moi.date_created,'%Y/%m/%d')";
            String groupName="date(moi.date_created)";
            if("hour".equals(groupBy)){
                dateFormat = "date_format(moi.date_created,'%H')";
                groupName="date_format(moi.date_created,'%H')";
            } else if("day".equals(groupBy)) {
                dateFormat = "date_format(moi.date_created,'%Y/%m/%d')";
                groupName="date(moi.date_created)";
            } else if("month".equals(groupBy)) {
                dateFormat = "date_format(moi.date_created,'%Y/%m')";
                groupName="date_format(moi.date_created,'%Y/%m')";
            }
            sql.append("SELECT "+dateFormat+" as dateStr,'sale' as chartType, sum(moi.buy_count) as quantity FROM merchant_order_item moi");
            sql.append(" LEFT JOIN product p ON p.id=moi.`product_id`");
            sql.append(" WHERE moi.is_delete='NO'");
            sql.append(" AND moi.`mall_id`=:mallId");
            sql.append(" AND moi.`merchant_id`=:merchantId");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            if (sourceType != null && MerchantOrderSourceType.ALL != MerchantOrderSourceType.valueOf(sourceType)) {
                if (MerchantOrderSourceType.SELF == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is null");
                }
                if (MerchantOrderSourceType.MARKET == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is not  null");
                }
            }

            if(productId!=null && productId.longValue()>0) {
                sql.append(" AND moi.`product_id`=:productId");
                params.put("productId", productId);
            }

            if (type != null && ProductType.valueOf(type) != ProductType.ALL) {
                sql.append(" AND p.`type` =:type");
                params.put("type", type);
            }
            if (startDate != null) {
                sql.append(" AND moi.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND moi.date_created<=:endDate");
                params.put("endDate", endDate);

            }
            sql.append(" group by "+groupName);
        };
        return doing.go(MerchantProductStatisticsDto.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 按月核销数量
     * @param mallId
     * @param merchantId
     * @param productId
     * @param sourceType
     * @param type
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public List<MerchantProductStatisticsDto> getProductVerifyCountByMonth(Long mallId, Long merchantId, Long productId, String sourceType, String type, String startDate, String endDate, String groupBy) {
        SQLListDoing<MerchantProductStatisticsDto> doing = (sql, fields, params, typeReferenceMap) -> {
            String dateFormat = "DATE_FORMAT(moi.date_created,'%Y/%m/%d')";
            String groupName = "date(moi.date_created)";
            if("hour".equals(groupBy)){
                dateFormat = "date_format(moi.date_created,'%H')";
                groupName="date_format(moi.date_created,'%H')";
            } else if("day".equals(groupBy)) {
                dateFormat = "date_format(moi.date_created,'%Y/%m/%d')";
                groupName="date(moi.date_created)";
            } else if("month".equals(groupBy)) {
                dateFormat = "date_format(moi.date_created,'%Y/%m')";
                groupName="date_format(moi.date_created,'%Y/%m')";
            }
            sql.append("SELECT "+dateFormat+" as dateStr,'verify' as chartType, sum(moi.verify_count) as quantity FROM merchant_order_item moi");
            sql.append(" LEFT JOIN product p ON p.id=moi.`product_id`");
            sql.append(" left join merchant_order mo on mo.id=moi.merchant_order_id");
            sql.append(" WHERE moi.is_delete='NO'");
            sql.append(" and moi.verify_count > 0");
            sql.append(" AND moi.`mall_id`=:mallId");
            sql.append(" AND moi.`merchant_id`=:merchantId");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            if (sourceType != null && MerchantOrderSourceType.ALL != MerchantOrderSourceType.valueOf(sourceType)) {
                if (MerchantOrderSourceType.SELF == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is null");
                }
                if (MerchantOrderSourceType.MARKET == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is not  null");
                }
            }

            if(productId!=null && productId.longValue()>0) {
                sql.append(" AND moi.`product_id`=:productId");
                params.put("productId", productId);
            }

            if (type != null && ProductType.valueOf(type) != ProductType.ALL) {
                sql.append(" AND p.`type` =:type");
                params.put("type", type);
            }
            if (startDate != null) {
                sql.append(" AND moi.date_created>=:startDate");
                params.put("startDate", startDate);
            }
            if (endDate != null) {
                sql.append(" AND moi.date_created<=:endDate");
                params.put("endDate", endDate);
            }
            sql.append(" group by "+groupName);
        };
        return doing.go(MerchantProductStatisticsDto.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 按月访问人数
     * @param mallId
     * @param merchantId
     * @param productId
     * @param sourceType
     * @param type
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public List<MerchantProductStatisticsDto> getProductViewCustomerCountByMonth(Long mallId, Long merchantId, Long productId, String sourceType, String type, String startDate, String endDate, String groupBy) {
        SQLListDoing<MerchantProductStatisticsDto> doing = (sql, fields, params, typeReferenceMap) -> {
            String dateFormat = "DATE_FORMAT(pe.date_created,'%Y/%m/%d')";
            String groupName = "date(pe.date_created)";
            if("hour".equals(groupBy)){
                dateFormat = "date_format(pe.date_created,'%H')";
                groupName="date_format(pe.date_created,'%H')";
            } else if("day".equals(groupBy)) {
                dateFormat = "date_format(pe.date_created,'%Y/%m/%d')";
                groupName="date(pe.date_created)";
            } else if("month".equals(groupBy)) {
                dateFormat = "date_format(pe.date_created,'%Y/%m')";
                groupName="date_format(pe.date_created,'%Y/%m')";
            }
            sql.append("SELECT "+dateFormat+" as dateStr,'visit' as chartType, count(distinct pe.buyer_id) as quantity FROM product_event pe");
            sql.append(" LEFT JOIN product p ON p.id=pe.`product_id`");
            sql.append(" WHERE pe.is_delete='NO'");
            sql.append(" AND pe.buyer_id is not null");
            sql.append(" AND pe.`mall_id`=:mallId");
            sql.append(" AND pe.`merchant_id`=:merchantId");
            sql.append(" AND pe.`event_type` IN('CLICKED')");

            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            if(productId!=null && productId.longValue()>0) {
                sql.append(" AND pe.`product_id`=:productId");
                params.put("productId", productId);
            }

            if (sourceType != null && MerchantOrderSourceType.ALL != MerchantOrderSourceType.valueOf(sourceType)) {
                if (MerchantOrderSourceType.SELF == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append(" AND p.market_id is null");
                }
                if (MerchantOrderSourceType.MARKET == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append(" AND p.market_id is not null");
                }
            }

            if (type != null && ProductType.valueOf(type) != ProductType.ALL) {
                sql.append(" AND p.type =:type");
                params.put("type", type);
            }
            if (startDate != null) {
                sql.append(" AND pe.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND pe.date_created<=:endDate");
                params.put("endDate", endDate);

            }
            sql.append(" group by "+groupName);
        };
        return doing.go(MerchantProductStatisticsDto.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 按月下单人数
     * @param mallId
     * @param merchantId
     * @param productId
     * @param sourceType
     * @param type
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public List<MerchantProductStatisticsDto> getProductSaleCustomerCountByMonth(Long mallId, Long merchantId, Long productId, String sourceType, String type, String startDate, String endDate, String groupBy) {
        SQLListDoing<MerchantProductStatisticsDto> doing = (sql, fields, params, typeReferenceMap) -> {
            String dateFormat = "DATE_FORMAT(moi.date_created,'%Y/%m/%d')";
            String groupName = "date(moi.date_created)";
            if("hour".equals(groupBy)){
                dateFormat = "date_format(moi.date_created,'%H')";
                groupName="date_format(moi.date_created,'%H')";
            } else if("day".equals(groupBy)) {
                dateFormat = "date_format(moi.date_created,'%Y/%m/%d')";
                groupName="date(moi.date_created)";
            } else if("month".equals(groupBy)) {
                dateFormat = "date_format(moi.date_created,'%Y/%m')";
                groupName="date_format(moi.date_created,'%Y/%m')";
            }
            sql.append("SELECT "+dateFormat+" as dateStr,'sale' as chartType, count(distinct moi.customer_id) as quantity FROM merchant_order_item moi");
            sql.append(" LEFT JOIN product p ON p.id=moi.`product_id`");
            sql.append(" WHERE moi.is_delete='NO'");
            sql.append(" AND moi.`mall_id`=:mallId");
            sql.append(" AND moi.`merchant_id`=:merchantId");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            if (sourceType != null && MerchantOrderSourceType.ALL != MerchantOrderSourceType.valueOf(sourceType)) {
                if (MerchantOrderSourceType.SELF == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is null");
                }
                if (MerchantOrderSourceType.MARKET == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is not  null");
                }
            }

            if(productId!=null && productId.longValue()>0) {
                sql.append(" AND moi.`product_id`=:productId");
                params.put("productId", productId);
            }

            if (type != null && ProductType.valueOf(type) != ProductType.ALL) {
                sql.append(" AND p.`type` =:type");
                params.put("type", type);
            }
            if (startDate != null) {
                sql.append(" AND moi.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND moi.date_created<=:endDate");
                params.put("endDate", endDate);

            }
            sql.append(" group by "+groupName);
        };
        return doing.go(MerchantProductStatisticsDto.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 按月核销人数
     * @param mallId
     * @param merchantId
     * @param productId
     * @param sourceType
     * @param type
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public List<MerchantProductStatisticsDto> getProductVerifyCustomerCountByMonth(Long mallId, Long merchantId, Long productId, String sourceType, String type, String startDate, String endDate, String groupBy) {
        SQLListDoing<MerchantProductStatisticsDto> doing = (sql, fields, params, typeReferenceMap) -> {
            String dateFormat = "DATE_FORMAT(moi.date_created,'%Y/%m/%d')";
            String groupName = "date(moi.date_created)";
            if("hour".equals(groupBy)){
                dateFormat = "date_format(moi.date_created,'%H')";
                groupName="date_format(moi.date_created,'%H')";
            } else if("day".equals(groupBy)) {
                dateFormat = "date_format(moi.date_created,'%Y/%m/%d')";
                groupName="date(moi.date_created)";
            } else if("month".equals(groupBy)) {
                dateFormat = "date_format(moi.date_created,'%Y/%m')";
                groupName="date_format(moi.date_created,'%Y/%m')";
            }

            sql.append("SELECT "+dateFormat+" as dateStr,'verify' as chartType, count(distinct moi.customer_id) as quantity FROM merchant_order_item moi");
            sql.append(" LEFT JOIN product p ON p.id=moi.`product_id`");
            sql.append(" left join merchant_order mo on mo.id=moi.merchant_order_id");
            sql.append(" WHERE moi.is_delete='NO'");
            sql.append(" and moi.verify_count > 0");
            sql.append(" AND moi.`mall_id`=:mallId");
            sql.append(" AND moi.`merchant_id`=:merchantId");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            if (sourceType != null && MerchantOrderSourceType.ALL != MerchantOrderSourceType.valueOf(sourceType)) {
                if (MerchantOrderSourceType.SELF == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is null");
                }
                if (MerchantOrderSourceType.MARKET == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is not  null");
                }
            }

            if(productId!=null && productId.longValue()>0) {
                sql.append(" AND moi.`product_id`=:productId");
                params.put("productId", productId);
            }

            if (type != null && ProductType.valueOf(type) != ProductType.ALL) {
                sql.append(" AND p.`type` =:type");
                params.put("type", type);
            }
            if (startDate != null) {
                sql.append(" AND moi.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND moi.date_created<=:endDate");
                params.put("endDate", endDate);

            }
            sql.append(" group by "+groupName);
        };

        return doing.go(MerchantProductStatisticsDto.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 产品详细数据
     * @param page
     * @param mallId
     * @param merchantId
     * @param productId
     * @param sourceType
     * @param type
     * @param startDate
     * @param endDate
     * @param keyword
     * @return
     */
    @Override
    public PageResult<MerchantProductStatisticsDto> getProductStatisticsList(PageResult page, Long mallId, Long merchantId, Long productId, String sourceType, String type, String startDate, String endDate, String keyword, Long channelId) {
        SQLPageDoing doing = (sql, fields, params, typeReferenceMap) -> {
            String startDateStr="";
            String endDateStr="";
            if(startDate!=null) {
                startDateStr=" and date_created>=:startDate ";
            }
            if(endDate!=null) {
                endDateStr=" and date_created<=:endDate ";
            }
            sql.append("select %s from (");
            sql.append(" select p.id,p.mall_id,p.merchant_id,p.name,p.status,p.market_id,p.type,p.sell_price from product p where p.market_id is null and p.mall_id=:mallId and p.merchant_id=:merchantId");
            sql.append(" union ");
            sql.append(" select pp.id,pp.mall_id,pp.merchant_id,pp.name,pp.status,pp.market_id,pp.type,pp.sell_price from product pp where pp.market_id is not null");
            sql.append(" and (pp.market_id in(select p.id from product p where p.market_id is null and p.mall_id=:mallId and p.merchant_id=:merchantId)");
            sql.append(" or (pp.mall_id=:mallId and pp.merchant_id=:merchantId))");
            sql.append(" ) a ");
            sql.append(" left join (select product_id,mall_id,merchant_id,sum(total_amount) as amount " +
                    " from merchant_order_item where mall_id=:mallId " +
                    startDateStr +
                    endDateStr +
                    " and merchant_id=:merchantId " +
                    " group by product_id,mall_id,merchant_id) moi");
            sql.append(" ON a.id=moi.`product_id`");
            sql.append(" left join channel_product cp on a.id=cp.product_id");

            sql.append(" WHERE a.status not in('WAIT_AUDIT','AUDIT_FAIL','DRAFT')");
//            sql.append(" and a.mall_id=:mallId");
//            sql.append(" and a.merchant_id=:merchantId");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            sql.append(" and cp.is_delete='NO'");
            if(channelId!=null && channelId.longValue()>0) {
                sql.append(" and cp.channel_id=:channelId");
                params.put("channelId", channelId);
            }

            if (sourceType != null && MerchantOrderSourceType.ALL != MerchantOrderSourceType.valueOf(sourceType)) {
                if (MerchantOrderSourceType.SELF == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND a.`market_id` is null");
                }
                if (MerchantOrderSourceType.MARKET == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND a.`market_id` is not  null");
                }
            }

            if(StringUtils.isNotEmpty(keyword)) {
                sql.append(" AND (a.`id`=:keyword or ");
                params.put("keyword", keyword);
                sql.append(" a.name like :keyword2)");
                params.put("keyword2", "%"+keyword+"%");
            }

            if(productId!=null && productId.longValue()>0) {
                sql.append(" AND a.`id`=:productId");
                params.put("productId", productId);
            }
            if (type != null && ProductType.valueOf(type) != ProductType.ALL) {
                sql.append(" AND a.`type` =:type");
                params.put("type", type);
            }
            if (startDate != null) {
                params.put("startDate", startDate);
            }
            if (endDate != null) {
                params.put("endDate", endDate);
            }
            sql.append(" order by moi.amount desc");
            fields.append("a.id as productId,a.name as productName,(case when a.market_id is null then 'SELF' else 'MARKET' end) as productSourceType,a.sell_price as salePrice,a.mall_id as mallId,a.merchant_id as merchantId");
        };
        return doing.go(page, MerchantProductStatisticsDto.class, objectMapper, jdbcTemplate, log);
    }

    /**
     * 下单人数
     * @param mallId
     * @param merchantId
     * @param productId
     * @param sourceType
     * @param type
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public Long getProductSaleCustomerCount(Long mallId, Long merchantId, Long productId, String sourceType, String type, String startDate, String endDate, Long channelId) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM merchant_order_item moi");
            sql.append(" LEFT JOIN product p ON p.id=moi.`product_id`");

            sql.append(" left join order_event oe on moi.merchant_order_num=oe.merchant_order_num");
            sql.append(" WHERE moi.is_delete='NO'");
            sql.append(" AND oe.event_type='ORDERED'");
            sql.append(" AND moi.`mall_id`=:mallId");
            sql.append(" AND moi.`merchant_id`=:merchantId");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            if(channelId!=null && channelId.longValue()>0) {
                sql.append(" AND moi.channel_id=:channelId");
                params.put("channelId", channelId);
            }

            if (sourceType != null && MerchantOrderSourceType.ALL != MerchantOrderSourceType.valueOf(sourceType)) {
                if (MerchantOrderSourceType.SELF == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is null");
                }
                if (MerchantOrderSourceType.MARKET == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is not  null");
                }
            }

            if(productId!=null && productId.longValue()>0) {
                sql.append(" AND moi.`product_id`=:productId");
                params.put("productId", productId);
            }

            if (type != null && ProductType.valueOf(type) != ProductType.ALL) {
                sql.append(" AND p.`type` =:type");
                params.put("type", type);
            }
            if (startDate != null) {
                sql.append(" AND moi.date_created>=:startDate");
                params.put("startDate", startDate);

            }
            if (endDate != null) {
                sql.append(" AND moi.date_created<=:endDate");
                params.put("endDate", endDate);

            }
            fields.append("count(distinct moi.customer_id)");
        };
        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 产品统计详细数据
     * @param page
     * @param mallId
     * @param merchantId
     * @param productId
     * @param sourceType
     * @param type
     * @param startDate
     * @param endDate
     * @param keyword
     * @return
     */
    @Override
    public PageResult<MerchantProductStatisticsDto> getProductsStatistics(PageResult page, Long mallId, Long merchantId, Long productId, String sourceType, String type, String startDate, String endDate, String keyword) {
        SQLPageDoing doing = (sql, fields, params, typeReferenceMap) -> {
            String startDateStr="";
            String endDateStr="";
            if(startDate!=null) {
                startDateStr=" and date_created>=:startDate ";
            }
            if(endDate!=null) {
                endDateStr=" and date_created<=:endDate ";
            }
            sql.append("SELECT %s FROM product p left join (select product_id,mall_id,merchant_id,sum(total_amount) as amount " +
                    " from merchant_order_item where mall_id=:mallId " +
                    startDateStr +
                    endDateStr +
                    " and merchant_id=:merchantId " +
                    " group by product_id,mall_id,merchant_id) moi");
            sql.append(" ON p.id=moi.`product_id`");
            sql.append(" WHERE p.status not in('WAIT_AUDIT','AUDIT_FAIL','DRAFT')");
            sql.append(" and p.mall_id=:mallId");
            sql.append(" and p.merchant_id=:merchantId");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            if (sourceType != null && MerchantOrderSourceType.ALL != MerchantOrderSourceType.valueOf(sourceType)) {
                if (MerchantOrderSourceType.SELF == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is null");
                }
                if (MerchantOrderSourceType.MARKET == MerchantOrderSourceType.valueOf(sourceType)) {
                    sql.append("  AND p.`market_id` is not  null");
                }
            }

            if(StringUtils.isNotEmpty(keyword)) {
                sql.append(" AND (p.`id`=:keyword or ");
                params.put("keyword", keyword);
                sql.append(" p.name like :keyword2)");
                params.put("keyword2", "%"+keyword+"%");
            }

            if(productId!=null && productId.longValue()>0) {
                sql.append(" AND p.`id`=:productId");
                params.put("productId", productId);
            }
            if (type != null && ProductType.valueOf(type) != ProductType.ALL) {
                sql.append(" AND p.`type` =:type");
                params.put("type", type);
            }
            if (startDate != null) {
                params.put("startDate", startDate);
            }
            if (endDate != null) {
                params.put("endDate", endDate);
            }
            sql.append(" order by moi.amount desc");
            fields.append("p.id as productId,p.name as productName,(case when p.market_id is null then 'SELF' else 'MARKET' end) as productSourceType,p.sell_price as salePrice");
        };
        return doing.go(page, MerchantProductStatisticsDto.class, objectMapper, jdbcTemplate, log);
    }
}